﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// Summary description for CustomerDAL
/// </summary>
public class CustomerDAL
{
    string str = ConfigurationManager.ConnectionStrings["SmartAccessories"].ToString();
    public CustomerDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public DataTable LoadCustomer()
    {
        SqlConnection cnn = new SqlConnection(str);
        cnn.Open();
        SqlDataAdapter da = new SqlDataAdapter("sp_loadCustomer", cnn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        DataSet ds = new DataSet();
        DataTable dt;
        try
        {
            da.Fill(ds);
            dt=ds.Tables[0];
            return dt;
            
        }
        catch 
        { 
            throw;
        }
        finally
        {
            ds.Dispose();
            da.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
    }
    public int insertCusromer(Customer c)
    {
        SqlConnection cnn = new SqlConnection(str);
        cnn.Open();
        SqlCommand cmd = new SqlCommand("sp_INSERT_CUSTOMER",cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@Identification", c.Identification);
            cmd.Parameters.AddWithValue("@firstname", c.Firstname);
            cmd.Parameters.AddWithValue("@lastname", c.Lastname);
            cmd.Parameters.AddWithValue("@age", c.Age);
            cmd.Parameters.AddWithValue("@address", c.Address);
            cmd.Parameters.AddWithValue("@phonenumber", c.Phonenumber);
            cmd.Parameters.AddWithValue("@email", c.Email);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            cmd.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
    }
    public int updateCustomer(Customer c)
    {
        SqlConnection cnn = new SqlConnection(str);
        cnn.Open();
        SqlCommand cmd = new SqlCommand("sp_UPDATE_CUSTOMER", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@Identification", c.Identification);
            cmd.Parameters.AddWithValue("@firstname", c.Firstname);
            cmd.Parameters.AddWithValue("@lastname", c.Lastname);
            cmd.Parameters.AddWithValue("@age", c.Age);
            cmd.Parameters.AddWithValue("@address", c.Address);
            cmd.Parameters.AddWithValue("@phonenumber", c.Phonenumber);
            cmd.Parameters.AddWithValue("@email", c.Email);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            cmd.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
    }
    public int deleteCustomer(Customer c)
    {
        SqlConnection cnn = new SqlConnection(str);
        cnn.Open();
        SqlCommand cmd = new SqlCommand("sp_DELETE_CUSTOMER", cnn);
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cmd.Parameters.AddWithValue("@Identification", c.Identification);
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            cmd.Dispose();
            cnn.Close();
            cnn.Dispose();
        }
    }
}